package db_java;

import java.awt.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;


//TODO replace methods *Company*


public class PersonDao implements Dao<Person> {
	
	Connection connection;
	CompanyDao companyDao;
	
	PersonDao(Connection connection, CompanyDao companyDao) {
		this.connection = connection;
		this.companyDao = companyDao;
	}
	

	@Override
	public Collection<Person> findAll() throws SQLException {
		ResultSet result = connection.createStatement().executeQuery("select t.*, c.* from clients t, company c where t.company_id=c.company_id");
		
		Collection<Person> resultList = new LinkedList<Person>();
		
		while (result.next()) { 
				resultList.add(new Person(result.getString(1), result.getString(2), new Company(result.getString(5), result.getString(6), result.getInt(4)))); 
		}
		return resultList;
	}
	

	@Override
	public Collection<Person> findByExample(Person example) throws SQLException {
		//TODO extract method and test 
		String sqlCon = sqlCondition(example);
		
		ResultSet result = connection.createStatement().executeQuery("select t.*, c.* from clients t, company c where t.company_id=c.company_id " + sqlCon);
		
		Collection<Person> resultList = new LinkedList<Person>();
		
		while (result.next()) { 
			resultList.add(new Person(result.getString(1), result.getString(2), new Company(result.getString(5), result.getString(6), result.getInt(4)))); 
		}
		return resultList;	
	}
	
	public static String sqlCondition(Person example) {
		String sqlCon = "";

		if (!example.getFirstName().isEmpty()) {
			sqlCon += " and client_fn = " + example.getFirstName();
		}
		if (!example.getLastName().isEmpty()) {
			sqlCon += " and client_ln = " + example.getLastName();
		}
		if (!example.getCompany().getCompanyName().isEmpty()) {
			sqlCon += " and company_name = " + example.getCompany().getCompanyName();
		}
		return sqlCon;
	}


//use findByExample from companyDao
	@Override
	public void insertInTable(Person entry) throws SQLException {
		String sqlString = "insert into clients values (?, ?, ?)";
		PreparedStatement statement = connection.prepareStatement(sqlString);
		statement.setString(1, entry.getFirstName());
		statement.setString(2, entry.getLastName());
		Collection<Company> companySearch = companyDao.findByExample(entry.getCompany());
		statement.setInt(3, companySearch.iterator().next().getId());
		//System.out.print((int)companySearch.toArray()[1]);
		statement.executeUpdate();		
	}

}
